﻿Imports System.Data.OracleClient
Imports System.IO

Public Class BSCSConfigReport_DAO
    Dim ExceptionRaised As Boolean

    Public Function GetDataTableFromCommand(_Command As OracleCommand) As DataTable

        Dim TableDataSet As New DataSet()
        Dim TableDataAdapter As New OracleDataAdapter()

        ExceptionRaised = False

        Dim TableConnection As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
        _Command.Connection = TableConnection
        Try
            TableConnection.Open()

            TableDataAdapter = New OracleDataAdapter(_Command)
            TableDataAdapter.Fill(TableDataSet, "Query")

        Catch ex As Exception

            ExceptionRaised = True
            Dim LengthofExMessage As Integer
            LengthofExMessage = ex.ToString.Length - 1
            If LengthofExMessage >= 3900 Then
                LengthofExMessage = 3900
            End If

            LogFailure(ex.ToString().Substring(0, LengthofExMessage))
        Finally
            TableConnection.Close()
        End Try
        If ExceptionRaised = False Then
            Return TableDataSet.Tables(0)
        Else
            Return Nothing
        End If

    End Function
    Public Function GetMenuDataTable() As DataTable

        Dim MenuTableCommand As New OracleCommand

        MenuTableCommand.CommandText = "EU_BILL.BSCS_CONFIG_REPORT_API.GetGroupList"
        MenuTableCommand.CommandType = CommandType.StoredProcedure
        MenuTableCommand.Parameters.Add("poc_GroupList", OracleType.Cursor).Direction = ParameterDirection.Output

        Return GetDataTableFromCommand(MenuTableCommand)
    End Function

    Public Function GetSubMenuDataTable(_GroupConfigId As Integer) As DataTable

        Dim MenuSubTableCommand As New OracleCommand

        MenuSubTableCommand.CommandText = "EU_BILL.BSCS_CONFIG_REPORT_API.GetSubGroupList"
        MenuSubTableCommand.CommandType = CommandType.StoredProcedure
        MenuSubTableCommand.Parameters.Add("pin_GroupId", OracleType.Number).Value = _GroupConfigId
        MenuSubTableCommand.Parameters.Add("poc_SubGroupList", OracleType.Cursor).Direction = ParameterDirection.Output

        Return GetDataTableFromCommand(MenuSubTableCommand)
    End Function

    Public Function GetConfigSQLDataTable(_ConfigId As Integer, _SortExpression As String, _PageIndex As Integer, _PageSize As Integer, _SearchStatement As String) As DataTable
        Dim ConfigSQLDataTableCommand As New OracleCommand

        ConfigSQLDataTableCommand.CommandText = "EU_BILL.BSCS_CONFIG_REPORT_API.GetConfigSqlResult"
        ConfigSQLDataTableCommand.CommandType = CommandType.StoredProcedure
        ConfigSQLDataTableCommand.Parameters.Add("pin_ConfigId", OracleType.Number).Value = _ConfigId
        ConfigSQLDataTableCommand.Parameters.Add("pis_SortExpression", OracleType.VarChar, 4000).Value = _SortExpression
        ConfigSQLDataTableCommand.Parameters.Add("pin_PageSize", OracleType.Number).Value = _PageSize
        ConfigSQLDataTableCommand.Parameters.Add("pin_PageNumber", OracleType.Number).Value = _PageIndex
        ConfigSQLDataTableCommand.Parameters.Add("pis_SearchStatement", OracleType.VarChar, 4000).Value = _SearchStatement
        ConfigSQLDataTableCommand.Parameters.Add("pin_UserId", OracleType.VarChar).Value = GlobalVar.userID
        ConfigSQLDataTableCommand.Parameters.Add("poc_ConfigSqlResult", OracleType.Cursor).Direction = ParameterDirection.Output

        Return GetDataTableFromCommand(ConfigSQLDataTableCommand)
    End Function

    Public Function GetUsageDescription(_ConfigId As Integer) As String

        Dim UsageDescriptionCommand As New OracleCommand

        UsageDescriptionCommand.CommandText = "EU_BILL.BSCS_CONFIG_REPORT_API.GetConfigUsage"
        UsageDescriptionCommand.CommandType = CommandType.StoredProcedure
        UsageDescriptionCommand.Parameters.Add("pin_ConfigId", OracleType.Number).Value = _ConfigId
        UsageDescriptionCommand.Parameters.Add("poc_UsageDescription", OracleType.Cursor).Direction = ParameterDirection.Output

        Return GetDataTableFromCommand(UsageDescriptionCommand).Rows(0).Item(0)
    End Function

    Public Function GetRowCount(_ConfigId As Integer, _SearchStatement As String) As Integer

        Dim RowCountCommand As New OracleCommand

        RowCountCommand.CommandText = "EU_BILL.BSCS_CONFIG_REPORT_API.GetConfigSqlRowCount"
        RowCountCommand.CommandType = CommandType.StoredProcedure
        RowCountCommand.Parameters.Add("pin_ConfigId", OracleType.Number).Value = _ConfigId
        RowCountCommand.Parameters.Add("pis_SearchStatement", OracleType.VarChar, 4000).Value = _SearchStatement
        RowCountCommand.Parameters.Add("pin_UserId", OracleType.VarChar).Value = GlobalVar.userID
        RowCountCommand.Parameters.Add("poc_ConfigSqlResult", OracleType.Cursor).Direction = ParameterDirection.Output

        ExceptionRaised = False
        Dim ReturnValueTable As DataTable
        ReturnValueTable = GetDataTableFromCommand(RowCountCommand)


        If ExceptionRaised = False Then
            Return ReturnValueTable.Rows(0).Item(0)
        End If

        Return 0

    End Function

    Public Function GetConfigSqlResultMetaData(_ConfigId As Integer) As DataTable

        Dim MetaDataCommand As New OracleCommand

        MetaDataCommand.CommandText = "EU_BILL.BSCS_CONFIG_REPORT_API.GetConfigSqlResultMetaData"
        MetaDataCommand.CommandType = CommandType.StoredProcedure
        MetaDataCommand.Parameters.Add("pin_ConfigId", OracleType.Number).Value = _ConfigId
        MetaDataCommand.Parameters.Add("pin_UserId", OracleType.VarChar).Value = GlobalVar.userID
        MetaDataCommand.Parameters.Add("poc_ConfigSqlResult", OracleType.Cursor).Direction = ParameterDirection.Output

        Return GetDataTableFromCommand(MetaDataCommand)
    End Function

    Public Sub ExecuteScalar(_Command As OracleCommand)

        Dim TableConnection As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
        _Command.Connection = TableConnection

        Try
            TableConnection.Open()
            _Command.ExecuteNonQuery()
        Catch ex As Exception
            Dim sw1 As System.IO.StreamWriter
            sw1 = File.AppendText("c:\ccapps\DbConnLog\" & Now.Today.ToString("yyyyMMdd") & ".txt")
            sw1.WriteLine(ex.Message)
            sw1.Flush()
            sw1.Close()
            sw1 = Nothing
        Finally
            TableConnection.Close()
        End Try

    End Sub

    Public Sub LogFailure(_LogMessage As String)

        Dim LogFailureCommand As New OracleCommand

        LogFailureCommand.CommandText = "EU_BILL.BSCS_CONFIG_REPORT_API.LogFailure"
        LogFailureCommand.CommandType = CommandType.StoredProcedure

        LogFailureCommand.Parameters.Add("pin_UserId", OracleType.VarChar).Value = GlobalVar.userID
        LogFailureCommand.Parameters.Add("pis_Remark", OracleType.VarChar, 4000).Value = _LogMessage

        ExecuteScalar(LogFailureCommand)

    End Sub

    Public Function GetHelpText(_ShortDescription As String) As String

        Dim RowCountCommand As New OracleCommand

        RowCountCommand.CommandText = "EU_BILL.BSCS_CONFIG_REPORT_API.GetHelpDescription"
        RowCountCommand.CommandType = CommandType.StoredProcedure
        RowCountCommand.Parameters.Add("pis_ShortDescription", OracleType.VarChar, 4000).Value = _ShortDescription
        RowCountCommand.Parameters.Add("poc_HelpText", OracleType.Cursor).Direction = ParameterDirection.Output

        Return GetDataTableFromCommand(RowCountCommand).Rows(0).Item(0)
    End Function

End Class



